<?php
//No direct access
defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.model');

class OwnserviceModelTransactions extends JModel
{
	/**
	 * Transactions data array
	 *
	 * @var array
	 */
	var $_data = null;

	/**
	 * Transactions total
	 *
	 * @var integer
	 */
	var $_total = null;

	/**
	 * Pagination object
	 *
	 * @var object
	 */
	var $_pagination = null;

	/**
	 * Transaction id
	 *
	 * @var int
	 */
	var $_id = null;

	/**
	 * Constructor
	 *
	 * @since 1.5
	 */

	function __construct()
	{
		parent::__construct();
		
		global $mainframe, $option;
		
		//get the number of entries from session
		$limit			= $mainframe->getUserStateFromRequest($option.'.transaction.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');		
		$limitstart		= $mainframe->getUserStateFromRequest($option.'.transaction.limitstart', 'limitstart', 0, 'int' );
		
		$limitstart 	= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);

		$array = JRequest::getVar('cid',  0, '', 'array');
		$this->setId((int)$array[0]);
	}

	/**
	 * Method to set the Transaction identifier
	 *
	 * @access	public
	 * @param	int Category identifier
	 */
	function setId($id)
	{
		// Set id and wipe data
		$this->_id	 = $id;
		$this->_data = null;
	}
	
	/**
	 * Method to get Data
	 *
	 * @access public
	 * @return mixed
	 */
	function getData()
	{
		// Lets load the transaction if it doesn't already exist
		if (empty($this->_data))
		{
			$query = $this->_buildQuery();
			$this->_db->setQuery($query);
			
			$this->_data = $this->_db->loadObjectList();
		}

		return $this->_data;
	}
	
	
	/**
	 * Total nr of Today transaction
	 *
	 * @access public
	 * @return integer
	 */
	function getTotal()
	{
		// Lets load the total nr if it doesn't already exist
		if (empty($this->_total))
		{
			$query = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);
		}

		return $this->_total;
	}
	
	/**
	 * Builds the query
	 *
	 * @access public
	 * @return string
	 */
	function _buildQuery() {
		
		// Get the WHERE and ORDER BY clauses for the query
		$where		= $this->_buildItemWhere();
		$orderby	= $this->_buildItemOrderBy();
		
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, c.iscore, c.code AS currencycode '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__users AS u ON u.id=t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id=u.id '
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
				.' LEFT JOIN #__ownservice_exchangerates AS e ON e.id=t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id=e.currency_id '
				. $where
				.' GROUP BY t.id '
				. $orderby
				;
				
				
		return $query;
	}
	
	/**
	 * Build the order clause
	 *
	 * @access private
	 * @return string
	 */
	function _buildItemOrderBy()
	{
		global $mainframe, $option;
		
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.transaction.filter_order', 'filter_order', 't.created', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.transaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		
		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		return $orderby;
	}
	
	/**
	 * Method to build the WHERE clause
	 *
	 * @access private
	 * @return array
	 */
	function _buildItemWhere( )
	{
		global $mainframe, $option;
		
		//Initialise variables
		$user		= & JFactory::getUser();
		$owuser = & Ownservice::getUser();
		$db		= & JFactory::getDbo();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
		$level		= $owuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.transaction.filter_branch','filter_branch',0,'int' );
		$search			= $mainframe->getUserStateFromRequest($option.'.transaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);
		
		//get current date
		//$now					= date('Y-m-d');
		//$now					= $date->toMySQL();
		$now						= date('Y-m-d H:i:s');
		list($dateA, $timeA)	= explode(' ', $now);
		list($y, $m, $d)		= explode('-', $dateA);

		$tomorrow 				= date('Y-m-d', mktime(0,0,0,$m,$d+1,$y));
		$where 					= array();
		
		$where[] 		= 't.state=1';
		$where[] 		= '(t.created >= '.$this->_db->Quote($dateA.' 00:00:00').' AND t.created <= '.$this->_db->Quote($tomorrow.' 23:59:59').')';
		
		//check access 
		if( ($user->gid < 25) &&  ($level < 5) ) {
			if( $owuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$owuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}	
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.transactionid LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );
		
		return $where;
		
	}

	/**
	 * Method to get user list
	 * @access public
	 * @return array object
	 * @since 1.5
	 */
	function getUsers() {

		//Initialise variables
		$user =& JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		// get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');

		$where = array();

		$where[] = 'u.block=0';
		$where[] = 'p.group_id = 1';

		if($gid < 25) {

			$canView	= Ownservice::checkAccess('transaction','view','transaction','all');
			$canViewOwn = Ownservice::checkAccess('transaction','view','transaction','own');
		
			if($canViewOwn){
				$where[] = ' u.id='.$user->get('id');
			}


			if($canView) {
				if($level > 1 && $level < 5) {
					$where[] = ' p.branch_id ='.$ownuser->get('branch_id');
				}
			}
		}
	
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$query = 'SELECT u.id, u.name '
				.' FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' LEFT JOIN #__ownservice_groups AS g ON g.id=p.group_id '
				.$where
				.' ORDER BY u.name';

		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectList();

		return $users;
	}

	/**
	 * Method to get Branches list
	 * 
	 * @access public
	 * @return array object
	 * @since 1.5
	 */
	function getBranches(){

		$query = 'SELECT * FROM #__ownservice_branches  '
				.' WHERE deleted=0 AND published = 1'
				.' ORDER BY name';

		$this->_db->setQuery($query);
		$branches = $this->_db->loadObjectList();

		return $branches;
	}

	/**
	 * Method to remove transactions
	 *
	 * @access	public
	 * @return	boolean	True on success
	 * @since	1.0
	 */
	function delete($id = 0)
	{
		$result = false;

		/*if ($id > 0)
		{
			
		}*/

		$where = ' WHERE '.(is_array($id)?( ' id IN ('.implode(',', $id).') ' ):' id='.$id);

		$query = 'DELETE FROM #__ownservice_transactions '.$where;

		$this->_db->setQuery( $query );
		if(!$this->_db->query()) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}

		return true;
	}

	/**
	 * Method get user single record
	 *
	 * @return object
	 * @since 1.5
	*/
	function getUser($id) {

		$query = ' SELECT p.*, u.name FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE u.id='.$id.' AND u.block = 0 ';
		$this->_db->setQuery($query);
		$profile = $this->_db->loadObject();

		return $profile;
	}

	/**
	 * Method get invoice for transaction
	 *
	 *@access private
	 *@since 1.5
	*/
	function _getInvoice($branchid=0)
	{
		$date1 = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
		$date2 = mktime(0, 0, 0, date('m'), date('d')+1, date('Y'));

		$where = ' WHERE ( created >= '
				.$this->_db->Quote(date('Y-m-d', $date1)." 04:00:00").' AND created <= '
				.$this->_db->Quote(date('Y-m-d', $date2)." 04:00:00").' )';

		if($branchid > 0)
		{
			$where .= ' AND branch_id='.$branchid;
		}
		$query = ' SELECT COUNT(*) FROM #__ownservice_transactions '
				.$where;

		$this->_db->setQuery($query);
		$count	= $this->_db->loadResult();
		
		return $count;
	}

	/**
	 * Method get branch for transaction
	 *
	 *@access private
	 *@since 1.5
	*/
	function _getBranch($uid)
	{
		$query = ' SELECT branch_id '
				.' FROM #__ownservice_users '
				.' WHERE user_id='.$uid;
		
		$this->_db->setQuery($query);
		$branch_id =$this->_db->loadResult();

		if($branch_id)
		{
			$query = ' SELECT * '
					.' FROM #__ownservice_branches '
					.' WHERE id='.$branch_id;

			$this->_db->setQuery($query);
			$branch = $this->_db->loadObject();

			if(is_object($branch))
			{
				$branchname = $branch->name;

				if($branch->agent_id)
				{
					$query = ' SELECT * '
					.' FROM #__ownservice_branches '
					.' WHERE id='.$branch->agent_id;

					$this->_db->setQuery($query);
					$agent = $this->_db->loadObject();

					if(is_object($agent))
					{
						//$branchname = $agent->name;
						$branch = $agent;
					}
				}
			}
		}

		return $branch;
	}

	/**
	 * Retrieval transfers of customer 
	*/
	function getCustomerTransfers()
	{
		global $mainframe, $option;
		
		//Initialise variables
		$user				= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();

		//get vars
		$gid				= $user->gid;
		$level				= $ownuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.customertransfers.filter_branch','filter_branch',0,'int' );
		$search			= $mainframe->getUserStateFromRequest($option.'.customertransfers.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);
		
		$where		= array();

		//check access 
		if( ($gid < 25) &&  ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch) {
				$where[] = 't.branch_id ='.$filter_branch;
			}
		}
		
		//filter search
		if($search) 
		{
			$where[]			=  ' (t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 				= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$filter_order			= $mainframe->getUserStateFromRequest($option.'.customertransfers.filter_order', 'filter_order', 't.created', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.customertransfers.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		
		$orderby 			= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, p.id AS memberid, p.birthday, u.name, u.username, '
					.' b.name AS branchname, c.iscore, a.account_name, a.bank_name, a.bank_other, a.branch_city, a.branch_name '
					.' FROM #__ownservice_customer_transactions AS t '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account AND a.user_id=t.user_id '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby;

		$limit			= $mainframe->getUserStateFromRequest($option.'.customertransfers.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart 	= $mainframe->getUserStateFromRequest($option.'.customertransfers.limitstart', 'limitstart', 0, 'int' );

		$limitstart	= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		$rows			= $this->_getList($query, $limitstart, $limit);
		
		return $rows;
	}

	/**
	 * Method get count number of customer transfers
	 *
	*/
	function getTotalCustomerTransfer()
	{
		global $mainframe, $option;
		
		//Initialise variables
		$user				= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();

		//get vars
		$gid				= $user->gid;
		$level				= $ownuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.customertransfers.filter_branch','filter_branch',0,'int' );
		$search			= $mainframe->getUserStateFromRequest($option.'.customertransfers.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);
		
		$where		= array();

		//check access 
		if( ($gid < 25) &&  ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch) {
				$where[] = 't.branch_id ='.$filter_branch;
			}
		}
		
		//filter search
		if($search) 
		{
			$where[]			=  ' (t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 				= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$filter_order			= $mainframe->getUserStateFromRequest($option.'.customertransfers.filter_order', 'filter_order', 't.created', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.customertransfers.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		
		$orderby 			= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, p.id AS memberid, p.birthday, u.name, u.username, '
					.' b.name AS branchname, c.iscore, a.bank_name, a.bank_other, a.branch_city '
					.' FROM #__ownservice_customer_transactions AS t '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby;
			
		$rows = $this->_getListCount($query);
	
		return $rows;
	}

	/**
	 * Method get transactions on status  Paid ( in process )
	 *
	 * @return subject
	 * @since 1.0
	*/
	function getPaidTransactions() {

		global $mainframe, $option;
		
		//Initialise variables
		$user		= & JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		//get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.paidtransactions.filter_branch','filter_branch',0,'int' );
		$search			= $mainframe->getUserStateFromRequest($option.'.paidtransactions.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);
		
		$where		= array();
		$where[]	= 't.state = 1 ';

		//check access 
		if( ($gid < 25) &&  ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch) {
				$where[] = 't.branch_id ='.$filter_branch;
			}
		}
		
		//filter search
		if($search) 
		{
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
							. ' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 				= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$filter_order			= $mainframe->getUserStateFromRequest($option.'.paidtransactions.filter_order', 'filter_order', 't.requested_date', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.paidtransactions.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		
		$orderby 			= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';
		
		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, p.id AS memberid, p.birthday, u.name, u.username, b.name AS branchname, '
				.' c.iscore, a.bank_name, a.bank_other, a.branch_city '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
				.' LEFT JOIN #__users AS u ON u.id = t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
				.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
				.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
				.$where
				.' GROUP BY t.id '
				.$orderby;
						
		//$limit			= $mainframe->getUserStateFromRequest($option.'.paidtransactions.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		//$limitstart 	= $mainframe->getUserStateFromRequest($option.'.paidtransactions.limitstart', 'limitstart', 0, 'int' );

		//$limitstart = ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
	
		//$rows = $this->_getList($query, $limitstart, $limit);
		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();
		
		return $rows;
	}

	/**
	 * Method to get the total number of transaction status is paid
	 *
	 * @access public
	 * @return integer
	 */
	function getTotalPaidTransactions()
	{
		global $mainframe, $option;
		
		//Initialise variables
		$user		= & JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		//get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.paidtransactions.filter_branch','filter_branch',0,'int' );
		$search			= $mainframe->getUserStateFromRequest($option.'.paidtransactions.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);
		
		$where		= array();
		$where[]	= 't.state = 1 ';

		//check access 
		if( ($gid < 25) &&  ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch) {
				$where[] = 't.branch_id ='.$filter_branch;
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$filter_order		= $mainframe->getUserStateFromRequest($option.'.paidtransactions.filter_order', 'filter_order', 't.requested_date', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.paidtransactions.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		
		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';
		
		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, p.id AS memberid, p.birthday, u.name, u.username, b.name AS branchname, c.iscore, a.bank_name, a.branch_city '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
				.' LEFT JOIN #__users AS u ON u.id = t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
				.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
				.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
				.$where
				.' GROUP BY t.id '
				.$orderby;

		$rows = $this->_getListCount($query);
	
		return $rows;
	}

	/**
	 * Method get transactions on status approved
	 *
	 * @return subject
	 * @since 1.0
	*/
	function getRequestapproval() {

		global $mainframe, $option;

		//Initialise variables
		$user		= & JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		//get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.requestapproval.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.requestapproval.filter_order', 'filter_order', 't.requested_date', 'cmd');
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.requestapproval.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search			= $mainframe->getUserStateFromRequest($option.'.requestapproval.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where		= array();
		$where[]	= 't.state = 2';

		//check access 
		if( ($gid < 25) &&  ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch) {
				$where[] = 't.branch_id ='.$filter_branch;
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';
		
		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, p.id AS memberid, p.birthday, u.name, u.username, b.name AS branchname, '
				.' b.code AS branchcode, c.iscore, a.bank_name AS abankname, a.bank_other, a.branch_name AS abranchname, a.branch_city '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__users AS u ON u.id = t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
				.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
				.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
				.$where
				.' GROUP BY t.id '
				.$orderby;
				
		
		$limit			= $mainframe->getUserStateFromRequest($option.'.requestapproval.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart		= $mainframe->getUserStateFromRequest($option.'.requestapproval.limitstart', 'limitstart', 0, 'int' );

		$limitstart = ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );

		$rows = $this->_getList($query, $limitstart, $limit);
	
		return $rows;
	}

	/**
	 * Method to get the total number of approved transactions
	 *
	 * @access public
	 * @return integer
	*/
	function getTotalRequestapproval() {
		
		global $mainframe, $option;

		//Initialise variables
		$user		= & JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		//get vars
		$gid		= $user->gid;
		$level		= $ownuser->get('level_id');
		
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.requestapproval.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.requestapproval.filter_order', 'filter_order', 't.requested_date', 'cmd');
		$filter_order_Dir	= $mainframe->getUserStateFromRequest($option.'.requestapproval.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search			= $mainframe->getUserStateFromRequest($option.'.requestapproval.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where		= array();
		$where[]	= 't.state = 2';

		//check access 
		if( ($gid < 25) &&  ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch) {
				$where[] = 't.branch_id ='.$filter_branch;
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';
		
		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, p.id AS memberid, p.birthday, u.name, u.username, b.name AS branchname, '
				.' b.code AS branchcode, c.iscore, a.bank_name AS abankname, a.branch_name AS abranchname, a.branch_city '
				.' FROM #__ownservice_transactions AS t '
				.' LEFT JOIN #__users AS u ON u.id = t.user_id '
				.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
				.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
				.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
				.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
				.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
				.$where
				.' GROUP BY t.id '
				.$orderby;

		return $this->_getListCount($query);
	}

	/**
	 * Method get user receiving transfer
	 *
	 * @return object
	 * @since 1.5
	*/
	function getReceiving() {

		$query = ' SELECT p.user_id AS id ,u.name FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE  u.block = 0 AND p.deleted = 0 AND p.group_id=3 ';
		$this->_db->setQuery($query);
		$users = $this->_db->loadObjectList();

		return is_array($users)?$users:array();
	}

	/**
	 * Method to get Failed Transactions
	 *
	 * @return object array
	 * @since 1.0
	*/
	function getFailedTransactions()
	{
		global $mainframe, $option;

		//Initialise variables
		$user	= & JFactory::getUser();
		$ownuser= & Ownservice::getUser();

		//get vavrs
		$gid	= $user->gid;
		$level	= $ownuser->get('level_id');

		//filter 
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.failedtransaction.filter_branch','filter_branch',0,'int' );
		$filter_order	= $mainframe->getUserStateFromRequest($option.'.failedtransaction.filter_order', 'filter_order', 't.invoice', 'cmd');
		$filter_order_dir= $mainframe->getUserStateFromRequest($option.'.failedtransaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search			= $mainframe->getUserStateFromRequest($option.'.failedtransaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where		= array();
		$where[]	= 't.state = '.$this->_db->Quote('5');
		
		//branch
		if($filter_branch)
		{
			$where[] = 't.branch_id='.$filter_branch;	
		}
		
		//check access 
		if( ($gid < 25) && ($level < 5)) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}

		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id=r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		$limit		= $mainframe->getUserStateFromRequest($option.'.failedtransaction.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart	= $mainframe->getUserStateFromRequest($option.'.failedtransaction.limitstart', 'limitstart', 0, 'int' );

		$limitstart	= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		$rows = $this->_getList($query, $limitstart, $limit);
		
		return $rows;
	}

	/**
	 * Method to get Total Failed Transactions
	 *
	 *
	 * @return int
	 * @since 1.0
	*/
	function getTotalFailedTransactions()
	{
		global $mainframe, $option;

		//Initialise variables
		$user	= & JFactory::getUser();
		$ownuser= & Ownservice::getUser();

		//get vavrs
		$gid	= $user->gid;
		$level	= $ownuser->get('level_id');

		//filter 
		$filter_branch	= $mainframe->getUserStateFromRequest($option.'.failedtransaction.filter_branch','filter_branch',0,'int' );
		$filter_order	= $mainframe->getUserStateFromRequest($option.'.failedtransaction.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir= $mainframe->getUserStateFromRequest($option.'.failedtransaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search			= $mainframe->getUserStateFromRequest($option.'.failedtransaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where		= array();
		$where[]	= 't.state = '.$this->_db->Quote('5');
		
		//branch
		if($filter_branch)
		{
			$where[] = 't.branch_id='.$filter_branch;	
		}
		
		//check access 
		if( ($gid < 25) && ($level < 5)) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}

		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.branch_city, c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		return $this->_getListCount($query);
	}

	/**
	 * Method to get Cancelled Transactions
	 *
	 * @return object array
	 * @since 1.0
	*/
	function getCancelledTransactions()
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		//filter 
		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where			= array();
		$where[]		= 't.state = '.$this->_db->Quote('8');
		
		//check access 
		if( ($gid < 25) && ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		$limit		= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart	= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.limitstart', 'limitstart', 0, 'int' );

		$limitstart	= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		$rows = $this->_getList($query, $limitstart, $limit);

		return $rows;
	}
	
	/**
	 * Method to get Total Cancelled Transactions
	 *
	 *
	 * @return int
	 * @since 1.0
	*/
	function getTotalCancelledTransactions()
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		//filter 
		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.cancelledtransaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where			= array();
		$where[]		= 't.state = '.$this->_db->Quote('8');
		
		//check access 
		if( ($gid < 25) && ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.branch_city, c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		return $this->_getListCount($query);
	}

	/**
	 * Method to get Corrected Transactions
	 *
	 * @return object array
	 * @since 1.0
	*/
	function getCorrectedTransactions()
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();
		$db				= & JFactory::getDbo();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		//filter 
		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where			= array();
		$where[]		= 't.state = '.$this->_db->Quote('6');
		

		//check access 
		if( ($gid < 25) && ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'p.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		$limit		= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart	= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.limitstart', 'limitstart', 0, 'int' );

		$limitstart	= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		$rows = $this->_getList($query, $limitstart, $limit);

		return $rows;
	}
	
	/**
	 * Method to get Total Corrected Transactions
	 *
	 *
	 * @return int
	 * @since 1.0
	*/
	function getTotalCorrectedTransactions()
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();
		$db				= & JFactory::getDbo();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		//filter 
		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.correctedtransaction.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where			= array();
		$where[]		= 't.state = '.$this->_db->Quote('6');
		

		//check access 
		if( ($gid < 25) && ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//filter branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}
		
		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*, u.username, p.id AS memberid, a.bank_name, a.branch_city, c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		return $this->_getListCount($query);
	}

	/**
	 * Method to get Request Approval Corrected
	 *
	 * @return object array
	 * @since 1.0
	*/
	function getRequestapprovalcorrected()
	{
		global $mainframe, $option;

		//Initialise variables
		$user	= & JFactory::getUser();
		$ownuser= & Ownservice::getUser();
		$db		= & JFactory::getDbo();

		//get vavrs
		$gid	= $user->gid;
		$level	= $ownuser->get('level_id');

		//filter 
		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where	= array();
		$where[] = 't.state = '.$this->_db->Quote('7');
		
		//check access 
		if( ($gid < 25) && ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}

		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*,  '
					.' u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, '
					.' c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;
					
		$limit			= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart		= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.limitstart', 'limitstart', 0, 'int' );

		$limitstart		= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		$rows = $this->_getList($query, $limitstart, $limit);
		
		return $rows;	
	}
	
	/**
	 * Method to get Total Request Approval Corrected
	 *
	 *
	 * @return int
	 * @since 1.0
	*/
	function getTotalRequestapprovalcorrected()
	{
		global $mainframe, $option;

		//Initialise variables
		$user	= & JFactory::getUser();
		$ownuser= & Ownservice::getUser();
		$db		= & JFactory::getDbo();

		//get vavrs
		$gid	= $user->gid;
		$level	= $ownuser->get('level_id');

		//filter 
		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.requestapprovalcorrected.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where	= array();
		$where[] = 't.state = '.$this->_db->Quote('7');
		
		//check access 
		if( ($gid < 25) && ($level < 5) ) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		} else {
			//branch
			if($filter_branch)
			{
				$where[] = 't.branch_id='.$filter_branch;	
			}
		}

		//filter search
		if($search) {
			$where[]  =  ' ( t.invoice LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).' OR '
						.' t.account LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false ).')';
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*,  '
					.' u.username, p.id AS memberid, a.bank_name, a.branch_city, '
					.' c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		return $this->_getListCount($query);
	}

	/**
	 * Method to Transferring
	 * 
	 * @return object array
	 * @since 1.0
	 */
	function getTransferring($cid = array())
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		//filter 
		$filter_order			= $mainframe->getUserStateFromRequest($option.'.transferring.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.transferring.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.transferring.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where			= array();
		$where[]		= 't.state = '.$this->_db->Quote('3');
		$where[]		= 't.transfer_id ='.$user->get('id');
		
		if (count( $cid ))
		{
			$where[] = 't.id IN ('.implode( ',', $cid ).')';
		}
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*,  '
					.' u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, a.account_name, a.account_type, a.phone_no, '
					.' c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account and a.user_id=u.id'
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.$orderby
					;

		//$limit		= $mainframe->getUserStateFromRequest($option.'.transferring.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		//$limitstart	= $mainframe->getUserStateFromRequest($option.'.transferring.limitstart', 'limitstart', 0, 'int' );

		//$limitstart	= ( $limit != 0 ? (floor($limitstart / $limit) * $limit) : 0 );
		
		//$rows = $this->_getList($query, $limitstart, $limit);

		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		return $rows;
	}

	/**
	 * Method to get date and session for transferring 
	 * 
	 * @return object array
	 * @since 1.0
	 */
	function getTransferringDateSession($cid = array())
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser	= & Ownservice::getUser();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		//filter 
		$filter_order			= $mainframe->getUserStateFromRequest($option.'.transferring.filter_order', 'filter_order', 't.invoice', 'cmd');;
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.transferring.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.transferring.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where		= array();
		$where[]		= 't.state = '.$this->_db->Quote('3');
		$where[]		= 't.transfer_id ='.$user->get('id');
		
		if (count( $cid ))
		{
			$where[] = 't.id IN ('.implode( ',', $cid ).')';
		}
		
		//Conditions
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		//Order
		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_dir.', t.created';

		//Get date of transfer
		$query		= ' SELECT t.requested_date FROM '
						 .' #__ownservice_transactions AS t '
						 .$where
						 .' GROUP BY DATE(t.requested_date) ' 
						 .' ORDER BY t.requested_date ASC '
						 ;
		
		$this->_db->setQuery($query);
		$datetransfers = $this->_db->loadObjectList();

		//Get session of transfer
		$transactions = array();
		$d = 0;
		if($datetransfers) 
		{
			foreach($datetransfers as $datetransfer)
			{
				$s = 0;
				list($date, $time)	= explode(' ', $datetransfer->requested_date);
				$transactions[$d]->requested_date	= $date;

				//set query
				$query	= ' SELECT t.session FROM '
							   .' #__ownservice_transactions AS t '
							   .$where
							   .' AND ( t.requested_date >= '.$this->_db->Quote($date.' 00:00:00').' AND t.requested_date <= '.$this->_db->Quote($date.' 23:59:59').')'
							   .' GROUP BY t.session '
							   .' ORDER BY t.session '
							  ;
						
				$this->_db->setQuery($query);
				$sessiontransfers = $this->_db->loadObjectList();
				if($sessiontransfers)
				{
					foreach($sessiontransfers as $sessiontransfer) {
						//Set session
						$transactions[$d]->session[$s]->session = $sessiontransfer->session;
						
						//Get data transfer
						$query = ' SELECT t.*,  '
									.' u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, a.account_name, a.account_type, a.phone_no, '
									.' c.iscore '
									.' FROM #__ownservice_transactions AS t '
									.' LEFT JOIN #__users AS u ON u.id = t.user_id '
									.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
									.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
									.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account and a.user_id = u.id'
									.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
									.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
									.$where
									.' AND ( t.requested_date >= '.$this->_db->Quote($transactions[$d]->requested_date.' 00:00:00').' AND t.requested_date <= '.$this->_db->Quote($transactions[$d]->requested_date.' 23:59:59').' ) '
									.' AND t.session='.(int) $sessiontransfer->session
									.' GROUP BY t.id '
									.$orderby
									;
						$this->_db->setQuery($query);
						
						//echo '<br />'.$query;
						
						$transactions[$d]->session[$s]->transactions = $this->_db->loadObjectList();	

						$s++;
					}
				}
				$d++;
			}
		}
		
		return $transactions;
	}

	/**
	 * Method to get approval transferred
	 *
	 * @return mixed 
	 * @since 1.5
	 */
	function getApprovalTransferred($cid)
	{
		global $mainframe, $option;

		//Initialise variables
		$user			= & JFactory::getUser();
		$ownuser		= & Ownservice::getUser();

		//get vavrs
		$gid			= $user->gid;
		$level			= $ownuser->get('level_id');

		$where		= array();
		$where[]		= ' t.id IN ('.implode(',', $cid).')';
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.*,  '
					.' u.username, u.name, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, a.account_name, a.account_type, a.phone_no, '
					.' c.iscore '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account and a.user_id=u.id'
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id '
					.' ORDER BY t.requested_date DESC '
					;

		$this->_db->setQuery($query);
		$rows = $this->_db->loadObjectList();

		return $rows;
	}
	
	/**
	 * Method to total Transferring
	 * 
	 * @return int
	 * @since 1.0
	 */
	function getTotalTransferring()
	{
		global $mainframe, $option;

		//Initialise variables
		$user				= & JFactory::getUser();
		$ownuser			= & Ownservice::getUser();

		//get vavrs
		$gid				= $user->gid;
		$level				= $ownuser->get('level_id');

		$filter_branch		= $mainframe->getUserStateFromRequest($option.'.transferring.filter_branch','filter_branch',0,'int' );
		$filter_order		= $mainframe->getUserStateFromRequest($option.'.transferring.filter_order', 'filter_order', 't.invoice', 'cmd');
		$filter_order_dir	= $mainframe->getUserStateFromRequest($option.'.transferring.filter_order_Dir', 'filter_order_Dir', 'DESC', 'cmd');
		$search				= $mainframe->getUserStateFromRequest($option.'.transferring.search', 'search', '', 'string' );
		if (strpos($search, '"') !== false) {
			$search = str_replace(array('=', '<'), '', $search);
		}
		$search = JString::strtolower($search);

		$where			= array();
		$where[]		= 't.state = '.$this->_db->Quote('3');
		$where[]		= 't.transfer_id ='.$user->get('id');
		
		//branch
		if($filter_branch)
		{
			$where[] = 't.branch_id='.$filter_branch;	
		}
		
		//check access 
		if($gid < 25) {
			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 't.branch_id ='.$ownuser->get('branch_id');
			}
		}

		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

		//Query to retrieve all categories that belong under the web links saction and that are published.
		$query = ' SELECT t.* '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.$where
					.' GROUP BY t.id ';

		return $this->_getListCount($query);
	}
	

	/**
	 * Method to Update Request Approval for transactions
	 * 
	 * @return 
	 * @since 1.0
	 */
	 function updateRequestApproval($cid=array())
	 {
	 	//Initialise variables
	 	$user					=& JFactory::getUser();
		$ownuser			=& Ownservice::getUser();
		$config				=& JFactory::getConfig();
		$tzoffset				=$config->getValue('config.offset');
	 	$date 				=& JFactory::getDate($dateAndTime, 10);
		
		//set var
		//$requested_date	= $date->toMySQL();
		$requested_date		= date('Y-m-d H:i:s');
		
		//get session
		$session	= $this->_getLastSession()+1;
	 	
		foreach ($cid as $id) 
		{	
			//update status transaction for Approval
			$query = 'UPDATE #__ownservice_transactions '
						.' SET state='.$this->_db->Quote('2')
						.', session='.$this->_db->Quote($session)
						.', requested_date='.$this->_db->Quote($requested_date)
						.' WHERE id='.$id;

			$this->_db->setQuery($query);
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true;
	 }

	 /**
	  * Method to get Session for transfer
	  *
	  * @return int
	  * @since 1.0
	  */
	  function _getLastSession()
	  {
			//Initialise variables
			$config		=& JFactory::getConfig();
			$tzoffset		=$config->getValue('config.offset');
	 		$date 		=& JFactory::getDate($dateAndTime, 10);
			$user			=& JFactory::getUser();
			$ownuser	=& Ownservice::getUser();

			//get date time now
			//$now					= $date->toMySQL();
			$now						= date('Y-m-d H:i:s');
			list($dateA, $timeA)	= explode(' ', $now);
			
			$where	= array();
			
			$where[] = '(requested_date >= '.$this->_db->Quote($dateA.' 00:00:00').' AND requested_date <= '.$this->_db->Quote($dateA.' 23:59:59').')';

			if( $ownuser->get('branch_id', 0) && $user->get('id', 0))
			{	
				$where[] = 'branch_id ='.$ownuser->get('branch_id');
			}

			$where = ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );

			$query = ' SELECT MAX(session) '
						.' FROM #__ownservice_transactions '
						.$where
						;

			$this->_db->setQuery($query);
			$lastsession = $this->_db->loadResult();
		
			return (int)$lastsession;
	  }
	 
	 /**
	 * Method to update Transferring for transactions
	 * 
	 * @return 
	 * @since 1.0
	 */
	 function updateTransferring($cid=array(), $transferrerid)
	 {
	 	//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
		
		//set var
		//$approval_date = $date->toMySQL();
		$approval_date = date('Y-m-d H:i:s');

	 	foreach ($cid as $id) 
		{	
			$query = ' UPDATE #__ownservice_transactions '
					.' SET state='.$this->_db->Quote('3')
					.',approval_date='.$this->_db->Quote($approval_date)
					.',transfer_id='.$transferrerid
					.' WHERE id='.$id;
					
			$this->_db->setQuery($query);
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true;
	 }
	 
	  /**
	 * Method to update failed for transactions
	 * 
	 * @return 
	 * @since 1.0
	 */
	 function updateFail($cid, $comment=array())
	 {
	 	//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
		
	 	foreach ($cid as $id) 
		{
			$c = '';
			
			if(isset($comment[$id]))
			{
				$c = ', comment='.$this->_db->Quote($comment[$id]);
			}
			
			$query = ' UPDATE #__ownservice_transactions '
					.' SET state='.$this->_db->Quote('5')
					.$c
					.' WHERE id='.$id
					;
					
			$this->_db->setQuery($query);
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true;
	 }

	function updateSuccess($cid, $comment=array())
	{
		//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
		
		//set var
		//$completion_date = $date->toMySQL();
		$completion_date = date('Y-m-d H:i:s');
		foreach ($cid as $id) 
		{
			$c = '';
			
			if(isset($comment[$id]))
			{
				$c = ', comment='.$this->_db->Quote($comment[$id]);
			}
			
			$query = ' UPDATE #__ownservice_transactions '
					.' SET state='.$this->_db->Quote('4')
					.' , completion_date='.$this->_db->Quote($completion_date)
					.$c
					.' WHERE id='.$id
					;
					
			$this->_db->setQuery($query);
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true; 		
	}
	
	/**
	 * Method to Update Transaction Corrected
	 * 
	 * @return boolean true false
	 * @since 1.0
	 */
	 function updateCorrected($cid)
	 {
	 	//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
			
		foreach ($cid as $id) 
		{
			$query = ' UPDATE #__ownservice_transactions '
					.' SET state='.$this->_db->Quote('6')
					.' WHERE id='.$id
					;
					
			$this->_db->setQuery($query);
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true;
	 }
	 
	 /**
	 * Method to Update Request Approval Corrected
	 * 
	 * @return boolean true false
	 * @since 1.0
	 */
	 function updateRequestApprovalCorrected($cid)
	 {
	 	//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
			
		foreach ($cid as $id) 
		{
			$query = ' UPDATE #__ownservice_transactions '
					.' SET state='.$this->_db->Quote('7')
					.' WHERE id='.$id
					;
					
			$this->_db->setQuery($query);
			//echo $this->_db->_sql;
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true;
	 }
	 
	 function updateApprove($cid)
	 {
	 	//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
			
		foreach ($cid as $id) 
		{
			$query = ' UPDATE #__ownservice_transactions '
					.' SET state='.$this->_db->Quote('3')
					.' WHERE id='.$id
					;
					
			$this->_db->setQuery($query);
			//echo $this->_db->_sql;
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		
		return true;
	 }
	 
	 /**
	  * Method to update state of transaction to cancelled
	  * 
	  * @return boolean
	  * @since 1.0
	  */
	 function updateCancelled($id)
	 {
	 	//Initialise variables
	 	$user 	=& JFactory::getUser();
		$ownuser=& Ownservice::getUser();
		$config	=& JFactory::getConfig();
		$tzoffset	=$config->getValue('config.offset');
	 	$date 	=& JFactory::getDate($dateAndTime, 10);
			
		$query = ' UPDATE #__ownservice_transactions '
				 .' SET state='.$this->_db->Quote('8')
				 .' WHERE id='.$id
				 ;
					
		$this->_db->setQuery($query);
		if(!$this->_db->query()) {
			$this->setError($this->_db->getErrorMsg());
			return false;
		}
		
		return true;
	 }

	 /**
	  * Method to get Transferring Transactions
	  *
	  * @return  object array
	  * @since 1.0
	 */
	 function getTransferringTransactions()
	{
		$transferrers = $this->getReceiving();

		$transferring = array();

		foreach($transferrers as $transferrer)
		{
			$data				= new stdClass();
			$data->id		= $transferrer->id;
			$data->name	= $transferrer->name;
			$transferring[$transferrer->id]['transferrer'] = $data;

			//Query to retrieve all categories that belong under the web links saction and that are published.
			$query = ' SELECT t.*,  '
					.' u.username, p.id AS memberid, a.bank_name, a.bank_other, a.branch_city, a.account_name, a.account_type, '
					.' c.iscore, c.code '
					.' FROM #__ownservice_transactions AS t '
					.' LEFT JOIN #__users AS u ON u.id = t.user_id '
					.' LEFT JOIN #__ownservice_users AS p ON p.user_id = u.id '
					.' LEFT JOIN #__ownservice_branches AS b ON b.id = p.branch_id '
					.' LEFT JOIN #__ownservice_accounts AS a ON a.account_no = t.account '
					.' LEFT JOIN #__ownservice_exchangerates AS r ON r.id = t.rate_id '
					.' LEFT JOIN #__ownservice_currencies AS c ON c.id = r.currency_id '
					.' WHERE t.state =3 AND t.transfer_id='.$transferrer->id
					.' GROUP BY t.id '
					.'ORDER BY t.created'
					;
		
			$this->_db->setQuery($query);
			$rows = $this->_db->loadObjectList();

			$transferring[$transferrer->id]['transactions'] = $rows;

		}

		return $transferring;
	 }

	 function getTransferred($id)
	{
		$query = ' SELECT p.user_id AS id ,u.name FROM #__ownservice_users AS p '
				.' LEFT JOIN #__users AS u ON u.id=p.user_id '
				.' WHERE  u.id ='.(int) $id;
		$this->_db->setQuery($query);
		$user = $this->_db->loadObject();

		return $user;
	 }
}